﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Web;

namespace WebApplication1
{
    public partial class NPOITest : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {

            }
        }

        protected void btnExport_Click(object sender, EventArgs e)
        {
            #region 将DataTable数据导入到Excel中
            int record = 200000;
            DataTable data = CreateDataTable(record);

            string fileName = "客户明细_" + DateTime.Now.ToString("MMddhhmmss") + ".xls";
            string sheetName = "客户明细";

            List<ExcelHead> headList = new List<ExcelHead>() {
                new ExcelHead() { Field = "ID", Name = "编号", FirstRow = 0, LastRow = 1, FirstCol = 0, LastCol = 0 },
                new ExcelHead() { Field = "", Name = "信息", FirstRow = 0, LastRow = 0, FirstCol = 1, LastCol = 2 },    //多表头
                new ExcelHead() { Field = "Name", Name = "名称", FirstRow = 1, LastRow = 1, FirstCol = 1, LastCol = 1 },
                new ExcelHead() { Field = "Sex", Name = "性别", FirstRow = 1, LastRow = 1, FirstCol = 2, LastCol = 2 },
                new ExcelHead() { Field = "CreateTime", Name = "创建时间", FirstRow = 0, LastRow = 1, FirstCol = 3, LastCol = 3 }
            };
            var buf = NpoiExcelHelper.DataTableToExcel(fileName, headList, data, sheetName);

            Response.Buffer = true;
            Response.Clear();
            Response.ClearHeaders();
            Response.ClearContent();
            Response.Charset = "UTF8";
            Response.ContentEncoding = Encoding.UTF8;
            Response.ContentType = "application/vnd.ms-excel";
            string browser = Request.Browser.Browser;
            if (browser.Contains("InternetExplorer"))
                Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8));
            else
                Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);

            Response.AddHeader("Content-Length", buf.Length.ToString());

            Response.Flush();
            Response.BinaryWrite(buf);
            #endregion
        }


        protected void btnImport_Click(object sender, EventArgs e)
        {
            //文件路径
            string file = Server.MapPath("客户明细_1125103958.xls");
            var dt = NpoiExcelHelper.ExcelToTable(file);
        }

        /// <summary>
        /// 创建DataTable对象
        /// </summary>
        public DataTable CreateDataTable(int record)
        {
            //创建DataTable
            DataTable dt = new DataTable("NewDt");

            //创建自增长的ID列
            DataColumn dc = dt.Columns.Add("ID", Type.GetType("System.Int32"));
            dc.AutoIncrement = true;  //自动增加
            dc.AutoIncrementSeed = 1; //起始为1
            dc.AutoIncrementStep = 1; //步长为1
            dc.AllowDBNull = false;  //非空

            //创建其它列表
            dt.Columns.Add(new DataColumn("Name", Type.GetType("System.String")));
            dt.Columns.Add(new DataColumn("Sex", Type.GetType("System.String")));
            dt.Columns.Add(new DataColumn("IsLogin", Type.GetType("System.Boolean")));
            dt.Columns.Add(new DataColumn("CreateTime", Type.GetType("System.DateTime")));

            DataRow dr;
            for (int i = 0; i < record; i++)
            {
                dr = dt.NewRow();
                dr["Name"] = "名字" + i.ToString();
                dr["Sex"] = "性别" + i.ToString();
                dr["IsLogin"] = true;
                dr["CreateTime"] = DateTime.Now;
                dt.Rows.Add(dr);
            }
            return dt;
        }
    }
}